﻿----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Function_Drop] @@Name = N'[_DB].[String_TranslitUrl]'
GO

----------------------------------------------------------------------------------------------------
--
--	Translit string to URL.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[String_TranslitUrl] (
	@@Value [nvarchar](max)
)
RETURNS [varchar](max)
AS
BEGIN
	DECLARE @Result [varchar](max) SET @Result = ''
	DECLARE @Source [varbinary](max) SET @Source = CONVERT([varbinary](max), @@Value)
	DECLARE @Index [int] SET @Index = 1
	DECLARE @UCS [binary](2) SET @UCS = NULL
	DECLARE @Char [nvarchar](4) SET @Char = NULL
	DECLARE @Delimiter [bit] SET @Delimiter = 0
	
	WHILE @Index <= LEN(@Source) BEGIN
		SELECT
			@UCS = SUBSTRING(@Source, @Index, 2),
			@Char = CONVERT([nchar](1), @UCS)

		IF @Char LIKE '[A-Z0-9]' BEGIN
			SET @Result = @Result + @Char
			SET @Delimiter = 0
		END ELSE BEGIN
			SELECT
				@Char = NULL

			SELECT
				@Char = [Translit]
			FROM
				[_DB].[Translit]
			WHERE
				[Char] = @UCS

			IF @Char IS NOT NULL BEGIN
				SET @Result = @Result + @Char
				SET @Delimiter = 0
			END ELSE IF @Delimiter = 0 BEGIN
				SET @Result = @Result + '-'
				SET @Delimiter = 1
			END
		END

		SET @Index = @Index + 2
	END
	
	IF RIGHT(@Result, 1) = '-' BEGIN
		SET @Result = LEFT(@Result, LEN(@Result) - 1)
	END

	RETURN @Result
END
GO
